using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;
using System.Data.SqlClient;


namespace BILab.Technical.BI.ModelManagement
{
	

	class CreateTableEngine
	{

		private SqlConnection _connection;
		private SqlPipe _pipe;
		private string _metadataConnectionString;
		private string _metadataTableName;


		private string _getTableListQuery = "select distinct(SL_TableName) as SL_TableName ";
		private string _getMetadataQuery ="select SL_TableName,SL_VariableName,SL_Type,SN_Size,SF_Identity,SF_AllowNull ";



		#region Properties

		public string MetadataTableName
		{
			get { return _metadataTableName; }
			set { _metadataTableName = value; }
		}

		public string GetMetadataQuery
		{
			get { return _getMetadataQuery; }
			set { _getMetadataQuery = value; }
		}


		public string GetTableListQuery
		{
			get { return _getTableListQuery; }
			set { _getTableListQuery = value; }
		}


		public string MetadataConnectionString
		{
			get { return _metadataConnectionString; }
			set { _metadataConnectionString = value; }
		}

		public SqlPipe Pipe
		{
			get { return _pipe; }
			set { _pipe = value; }
		}

		public SqlConnection Connection
		{
			get { return _connection; }
			set { _connection = value; }
		}
		#endregion

		public void Process(SqlString MetadataConnectionString,SqlString MetadataTableName, SqlConnection Connection , SqlPipe Pipe)
		{
			DataSet metadataDs = new DataSet("Metadata");
			DataRow[] columnList;
			DataTable tableList;
			DataTable fullColumnList;

			string tableName;
			string statement;

			OleDbDataAdapter adapter;
			StringBuilder sb;
			OleDbCommand command;

			SqlCommand sqlCommand;
			int rowsAffected;

			CreateTableStatementBuilder ctsb;

			
			this.Connection = Connection;
			this.Pipe = Pipe;
			this.MetadataConnectionString = MetadataConnectionString.ToString();

			/* Opens Metadata Connection */
			OleDbConnection oledbconn = new OleDbConnection(this.MetadataConnectionString);
			oledbconn.Open();
			
			/* Get the table List */
			sb = new StringBuilder();
			sb.Append(this.GetTableListQuery);
			sb.Append(" from ");
			sb.Append(MetadataTableName);
			command = new OleDbCommand(sb.ToString());
			command.Connection = oledbconn;

			adapter= new OleDbDataAdapter(command);
			adapter.Fill(metadataDs, "TableList");

			/* Get the Metadata */
			sb = new StringBuilder();
			sb.Append(this.GetMetadataQuery);
			sb.Append(" from ");
			sb.Append(MetadataTableName);
			command = new OleDbCommand(sb.ToString());
			command.Connection = oledbconn;

			adapter = new OleDbDataAdapter(command);
			adapter.Fill(metadataDs, "FullColumnList");

			/* Closes the connection */
			oledbconn.Close();

			/* iterates into table list in order to create all table */
			
			tableList = metadataDs.Tables["TableList"];
			fullColumnList = metadataDs.Tables["FullColumnList"];

			ctsb = new CreateTableStatementBuilder();

			Connection.Open();
			foreach (DataRow dataRow in tableList.Rows)
			{
				/* for each table named into the metadata ... */
				tableName = (string)dataRow["SL_TableName"];
				/* we filter the full metadata on the current Table ... */
				columnList = fullColumnList.Select("SL_TableName = '" + tableName + "'");

				/* Prior to creation, we've to delete the table */
				sb = new StringBuilder();
				sb.Append("Drop Table ");
				sb.Append(tableName);
				statement = sb.ToString();
				Pipe.Send("MPCreate Model will delete  : " + tableName);
				sqlCommand = new SqlCommand(sb.ToString(), Connection);
				rowsAffected = sqlCommand.ExecuteNonQuery();

				/* then we create the variable */
				statement = ctsb.BuildStatement(tableName, columnList);

				Pipe.Send("MPCreate Model will execute : " + sb.ToString());
				sqlCommand = new SqlCommand(sb.ToString(), Connection);
				rowsAffected = sqlCommand.ExecuteNonQuery();
			}
			Connection.Close();

		}

	}
}
